# 业务类参数计算
import asyncio
import logging
import time

import pandas as pd

from app.model_phl import utils_sql, utils, utils_json


async def cal_biz_last_refuse001(phone, apply_date):
    return utils_sql.get_mysql_last_refuse001(phone=phone, apply_date=apply_date)


async def cal_biz_register_cnt(
        apply_date,
        app_id,
        phone,
        days,
        device_dict
):
    """
    注册数量统计
    """
    imei = device_dict.get('imei')
    imsi = device_dict.get('imsi')
    gaid = device_dict.get('gaid')

    return utils_sql.get_mysql_register_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        days=days,
        imei=imei if imei else '-999',
        imsi=imsi if imsi else '-999',
        gaid=gaid if gaid else '-999'
    )


async def cal_biz_ip_register_cnt(apply_date, app_id, days, device_dict):
    """
    注册数量统计(根据GPS)
    """
    ip = device_dict.get('ip')

    return utils_sql.get_mysql_register_ip_cnt(
        apply_date=apply_date,
        app_id=app_id,
        days=days,
        ip=ip if ip else '-999',
    )


async def cal_biz_fbid_cnt(user_id, app_id, apply_date):
    return utils_sql.get_mysql_fbid_cnt(
        user_id=user_id,
        app_id=app_id,
        apply_date=apply_date,
    )


async def cal_biz_devLoanCnt(apply_date, app_id, phone, device_dict):
    """
    在贷未还数量统计
    """
    imei = device_dict.get('imei')
    imsi = device_dict.get('imsi')
    ip = device_dict.get('ip')
    gaid = device_dict.get('gaid')

    return utils_sql.get_mysql_loan_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        imei=imei if imei else '-999',
        imsi=imsi if imsi else '-999',
        ip=ip if ip else '-999',
        gaid=gaid if gaid else '-999',
    )


async def cal_biz_black_GidDevice(apply_date, app_id, phone, device_dict):
    imei = device_dict.get('imei')
    imsi = device_dict.get('imsi')
    ip = device_dict.get('ip')
    gaid = device_dict.get('gaid')

    return utils_sql.get_mysql_overdue_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        imei=imei if imei else '-999',
        imsi=imsi if imsi else '-999',
        ip=ip if ip else '-999',
        gaid=gaid if gaid else '-999',
    )


async def cal_bankcard_associated_phone_cnt(user_id, apply_date):
    return utils_sql.get_mysql_bankcard_phone_cnt(
        user_id=user_id,
        apply_date=apply_date,
    )


async def cal_major_phone_majorfraud_cnt(user_id):
    major_fraud_df = utils_json.get_major_fraud_data()
    # print(major_fraud_df.keys())
    connections = utils_sql.get_mysql_social_connections(user_id=user_id)
    # print(connections.keys())
    if len(connections) == 0:
        return 0
    return major_fraud_df.loc[(major_fraud_df.get('Phone').isin([
        connections.get('Phone1'),
        connections.get('Phone2'),
        connections.get('Phone3'),
        connections.get('Phone4'),
    ]))].get('Phone').count()


async def cal_inner_black_phone(phone, apply_date):
    return utils_sql.get_mysql_inner_black_phone_cnt(phone=phone, apply_date=apply_date)


async def cal_inner_black_idCard(idcard, apply_date):
    return utils_sql.get_mysql_inner_black_card_cnt(idcard=idcard, apply_date=apply_date)


async def cal_biz_curDefault(phone, app_id, apply_date):
    cnt = utils_sql.get_mysql_cur_default_cnt(phone=phone, app_id=app_id, apply_date=apply_date)
    return 1 if cnt > 0 else 0


async def cal_biz_maxDpd(phone, app_id, apply_date):
    return utils_sql.get_mysql_maxDpd(phone=phone, app_id=app_id, apply_date=apply_date)


async def cal_biz_lastOrderDpd(phone, app_id, apply_date):
    return utils_sql.get_mysql_lastOrderDpd(phone=phone, app_id=app_id, apply_date=apply_date)


async def cal_whitelist_first_time_diff_days(phone, apply_date):
    return utils_sql.get_mysql_whitelist_first_time_diff_days(phone=phone, apply_date=apply_date)


async def cal_week_number_of_loans(phone, apply_no, apply_date):
    end_date = apply_date
    start_date = apply_date - pd.Timedelta(days=7)
    return utils_sql.get_mysql_week_number_of_loans(
        phone=phone, apply_no=apply_no, start_date=start_date, end_date=end_date
    )


async def cal_now_overdue_num(user_id, app_id, apply_no, apply_date):
    """
    本平台当前逾期情况：检索客户当前最新一笔订单是否逾期，逾期返回1，未逾期返回0
    """
    cnt = utils_sql.get_mysql_now_overdue_cnt(user_id=user_id, app_id=app_id, apply_no=apply_no, apply_date=apply_date)
    return 1 if cnt > 0 else 0


async def cal_max_overdue_days(phone, app_id, apply_date):
    return utils_sql.get_mysql_max_overdue_days(phone=phone, app_id=app_id, apply_date=apply_date)


async def cal_on_loan_not_yet_cnt(project_id, user_id, apply_date):
    # 当前产品的在贷未还笔数:on_loan_not_yet_cnt
    return utils_sql.get_mysql_loan_not_yet_cnt(project_id=project_id, user_id=user_id, apply_date=apply_date)


async def cal_week_apply_num(user_id, app_id, apply_no, apply_date):
    end_date = apply_date
    start_date = apply_date - pd.Timedelta(days=7)
    return utils_sql.get_mysql_week_apply_num(
        user_id=user_id, app_id=app_id, apply_no=apply_no, start_date=start_date, end_date=end_date
    )


async def cal_biz_max_expire_day_all_phone_cur(phone, apply_date):
    return utils_sql.get_mysql_max_expire_day_all_phone_cur(phone=phone, apply_date=apply_date)


async def cal_biz_max_expire_day_all_phone_history(phone, apply_date):
    return utils_sql.get_mysql_max_expire_day_all_phone_history(phone=phone, apply_date=apply_date)


async def cal_biz_max_expire_day_all_real_idcard_history(idcard, apply_date):
    return utils_sql.get_mysql_max_expire_day_all_real_idcard_history(idcard=idcard, apply_date=apply_date)


def default_features():
    """
    默认特征值
    """
    return {
        # 与上次拒绝时间间隔:last_refuse001
        'last_refuse001': None,
        # 注册账户数:biz_devRegCnt
        'biz_devRegCnt': None,
        # 30天内注册账户数:biz_devRegCnt_1m
        'biz_devRegCnt_1m': None,
        # 1周内注册账户数:biz_devRegCnt_1w
        'biz_devRegCnt_1w': None,
        # 1天内注册账户数:biz_devRegCnt_1d
        'biz_devRegCnt_1d': None,
        # 同GPS（小数点5位）24小时内注册或完成必填项客户:biz_gpsRegCnt_1d
        'biz_gpsRegCnt_1d': None,
        # 1天内注册账户数（同Ip）:biz_ipRegCnt_1d
        'biz_ipRegCnt_1d': None,
        # 在贷单数:biz_devLoanCnt
        'biz_devLoanCnt': None,
        # 逾期订单数:biz_black_GidDevice
        'biz_black_GidDevice': None,
        # 同一facebook id累计用户数:biz_fbid_cnt
        'biz_fbid_cnt': None,
        # 同一银行卡号关联不同手机号个数:bankcard_associated_phone_cnt
        'bankcard_associated_phone_cnt': None,
        # 四个亲密联系人中majorfraud号码的个数:major_phone_majorfraud_cnt
        'major_phone_majorfraud_cnt': None,
        # 通讯录号码中命中逾期客户的个数:con_contact_dpd20_cnt
        'con_contact_dpd20_cnt': None,
        # 手机号命中黑名数:inner_black_phone
        'inner_black_phone': None,
        # 身份证命中黑名单数:inner_black_idCard
        'inner_black_idCard': None,
        # 设备号是否在内部黑名单中 macCode:inner_black_macCode
        'inner_black_macCode': None,
        # 设备号是否在内部黑名单中 gaid:inner_black_gaid
        'inner_black_gaid': None,
        # 设备号是否在内部黑名单中 ip:inner_black_ip
        'inner_black_ip': None,
        # 设备号是否在内部黑名单中 gps:inner_black_gps
        'inner_black_gps': None,
        # 身份证是否精准匹配:outer_black_blurry_idcard
        'outer_black_blurry_idcard': None,
        # 生日姓名模糊匹配:outer_black_blurry_namebirthday
        'outer_black_blurry_namebirthday': None,
        # 对应的order 当前最大逾期天数:biz_black_GidDevice_all
        'biz_black_GidDevice_all': None,
        # 全平台当前是否处于延期中且未还款（基于注册号码）:extension_nopay_all_phone
        'extension_nopay_all_phone': None,
        # 当前是否处于逾期中（是：1，否：0）:biz_curDefault
        'biz_curDefault': None,
        # 历史最大逾期天数:biz_maxDpd
        'biz_maxDpd': None,
        # 最近一笔结清交易逾期天数:biz_lastOrderDpd
        'biz_lastOrderDpd': None,
        # 已结清订单逾期天数最大值:max_overdue_days
        'max_overdue_days': None,
        # 白名单首次日期距离申请时间天数校验:whitelist_first_time_diff_days
        'whitelist_first_time_diff_days': None,
        # 本平台近一周该客户放款笔数:week_number_of_loans
        'week_number_of_loans': None,
        # 本平台当前逾期情况:now_overdue_num
        'now_overdue_num': None,
        # 当前产品的在贷未还笔数:on_loan_not_yet_cnt
        'on_loan_not_yet_cnt': None,
        # 本平台近一周内申请订单数:week_apply_num
        'week_apply_num': None,
        # 全平台当前未还交易逾期天数（基于注册号码）:biz_max_expire_day_all_phone_cur
        'biz_max_expire_day_all_phone_cur': None,
        # 全平台最大逾期天数（基于注册号码）:biz_max_expire_day_all_phone_history
        'biz_max_expire_day_all_phone_history': None,
        # 全平台最大逾期天数（基于最终身份证号）:biz_max_expire_day_all_real_idcard_history
        'biz_max_expire_day_all_real_idcard_history': None,
    }


async def cal_biz_features(apply_no, phone, apply_date, user_id, app_id, project_id, device_dict, enable=False):
    # 默认值
    data = default_features()
    if enable is False:
        return data
    # 身份证号
    idcard = utils_sql.get_mysql_idcard(user_id=user_id)

    # 申请时间
    apply_date = pd.to_datetime(apply_date) if apply_date is not None else pd.Timestamp.now()

    # 与上次拒绝时间间隔:last_refuse001
    last_refuse001 = asyncio.create_task(cal_biz_last_refuse001(
        phone=phone, apply_date=apply_date
    ))
    # 注册账户数:biz_devRegCnt
    biz_devRegCnt = asyncio.create_task(cal_biz_register_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        days=0,
        device_dict=device_dict
    ))
    # 30天内注册账户数:biz_devRegCnt_1m
    biz_devRegCnt_1m = asyncio.create_task(cal_biz_register_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        days=30,
        device_dict=device_dict
    ))
    # 7天内注册账户数:biz_devRegCnt_1w
    biz_devRegCnt_1w = asyncio.create_task(cal_biz_register_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        days=7,
        device_dict=device_dict
    ))
    # 1天内注册账户数:biz_devRegCnt_1d
    biz_devRegCnt_1d = asyncio.create_task(cal_biz_register_cnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        days=1,
        device_dict=device_dict
    ))
    # 1天内注册账户数（同Ip）:biz_ipRegCnt_1d
    biz_ipRegCnt_1d = asyncio.create_task(cal_biz_ip_register_cnt(
        apply_date=apply_date,
        app_id=app_id,
        days=1,
        device_dict=device_dict
    ))
    # 在贷单数数:biz_devLoanCnt
    biz_devLoanCnt = asyncio.create_task(cal_biz_devLoanCnt(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        device_dict=device_dict
    ))
    # 逾期订单数:biz_black_GidDevice
    biz_black_GidDevice = asyncio.create_task(cal_biz_black_GidDevice(
        apply_date=apply_date,
        app_id=app_id,
        phone=phone,
        device_dict=device_dict
    ))
    # 同一facebook id累计用户数:biz_fbid_cnt
    biz_fbid_cnt = asyncio.create_task(cal_biz_fbid_cnt(
        user_id=user_id,
        app_id=app_id,
        apply_date=apply_date,
    ))
    # 同一银行卡号关联不同手机号个数:bankcard_associated_phone_cnt
    bankcard_associated_phone_cnt = asyncio.create_task(cal_bankcard_associated_phone_cnt(
        user_id=user_id,
        apply_date=apply_date,
    ))
    # 四个亲密联系人中majorfraud号码的个数:major_phone_majorfraud_cnt
    major_phone_majorfraud_cnt = asyncio.create_task(cal_major_phone_majorfraud_cnt(
        user_id=user_id
    ))
    # 手机号命中黑名单数:inner_black_phone
    inner_black_phone = asyncio.create_task(cal_inner_black_phone(
        phone=phone, apply_date=apply_date
    ))
    # 身份证命中黑名单数:inner_black_idCard
    inner_black_idCard = asyncio.create_task(cal_inner_black_idCard(
        idcard=idcard, apply_date=apply_date
    ))
    # 当前是否处于逾期中（是：1，否：0）:biz_curDefault
    biz_curDefault = asyncio.create_task(cal_biz_curDefault(
        phone=phone, app_id=app_id, apply_date=apply_date
    ))
    # 历史最大逾期天数:biz_maxDpd
    biz_maxDpd = asyncio.create_task(cal_biz_maxDpd(
        phone=phone, app_id=app_id, apply_date=apply_date
    ))
    # 最近一笔结清交易逾期天数:biz_lastOrderDpd
    biz_lastOrderDpd = asyncio.create_task(cal_biz_lastOrderDpd(
        phone=phone, app_id=app_id, apply_date=apply_date
    ))
    # 已结清订单逾期天数最大值:max_overdue_days
    max_overdue_days = asyncio.create_task(cal_max_overdue_days(
        phone=phone, app_id=app_id, apply_date=apply_date
    ))
    # 白名单首次日期距离申请时间天数校验:whitelist_first_time_diff_days
    whitelist_first_time_diff_days = asyncio.create_task(cal_whitelist_first_time_diff_days(
        phone=phone, apply_date=apply_date
    ))
    # 本平台近一周该客户放款笔数:week_number_of_loans
    week_number_of_loans = asyncio.create_task(cal_week_number_of_loans(
        phone=phone, apply_no=apply_no, apply_date=apply_date
    ))
    # 本平台当前逾期情况：检索客户当前最新一笔订单是否逾期，逾期返回1，未逾期返回0:now_overdue_num
    now_overdue_num = asyncio.create_task(cal_now_overdue_num(
        user_id=user_id, app_id=app_id, apply_no=apply_no, apply_date=apply_date
    ))
    # 当前产品的在贷未还笔数:on_loan_not_yet_cnt
    on_loan_not_yet_cnt = asyncio.create_task(cal_on_loan_not_yet_cnt(
        project_id=project_id, user_id=user_id, apply_date=apply_date
    ))
    # 本平台近一周内申请订单数:week_apply_num
    week_apply_num = asyncio.create_task(cal_week_apply_num(
        user_id=user_id, app_id=app_id, apply_no=apply_no, apply_date=apply_date
    ))
    # 全平台当前未还交易逾期天数（基于注册号码）:biz_max_expire_day_all_phone_cur
    biz_max_expire_day_all_phone_cur = asyncio.create_task(cal_biz_max_expire_day_all_phone_cur(
        phone=phone, apply_date=apply_date
    ))
    # 全平台最大逾期天数（基于注册号码）:biz_max_expire_day_all_phone_history
    biz_max_expire_day_all_phone_history = asyncio.create_task(cal_biz_max_expire_day_all_phone_history(
        phone=phone, apply_date=apply_date
    ))
    # 全平台最大逾期天数（基于最终身份证号）:biz_max_expire_day_all_real_idcard_history
    biz_max_expire_day_all_real_idcard_history = asyncio.create_task(cal_biz_max_expire_day_all_real_idcard_history(
        idcard=idcard, apply_date=apply_date
    ))
    # print("biz_max_expire_day_all_real_idcard_history 程序运行时长：", time.time() - start_time, "秒")
    data.update({
        # 与上次拒绝时间间隔:last_refuse001
        'last_refuse001': await last_refuse001,
        # 注册账户数:biz_devRegCnt
        'biz_devRegCnt': await biz_devRegCnt,
        # 30天内注册账户数:biz_devRegCnt_1m
        'biz_devRegCnt_1m': await biz_devRegCnt_1m,
        # 1周内注册账户数:biz_devRegCnt_1w
        'biz_devRegCnt_1w': await biz_devRegCnt_1w,
        # 1天内注册账户数:biz_devRegCnt_1d
        'biz_devRegCnt_1d': await biz_devRegCnt_1d,
        # 1天内注册账户数（同Ip）:biz_ipRegCnt_1d
        'biz_ipRegCnt_1d': await biz_ipRegCnt_1d,
        # 在贷单数:biz_devLoanCnt
        'biz_devLoanCnt': await biz_devLoanCnt,
        # 逾期订单数:biz_black_GidDevice
        'biz_black_GidDevice': await biz_black_GidDevice,
        # 同一facebook id累计用户数:biz_fbid_cnt
        'biz_fbid_cnt': await biz_fbid_cnt,
        # 同一银行卡号关联不同手机号个数:bankcard_associated_phone_cnt
        'bankcard_associated_phone_cnt': await bankcard_associated_phone_cnt,
        # 四个亲密联系人中majorfraud号码的个数:major_phone_majorfraud_cnt
        'major_phone_majorfraud_cnt': await major_phone_majorfraud_cnt,
        # 当前是否处于逾期中（是：1，否：0）:biz_curDefault
        'biz_curDefault': await biz_curDefault,
        # 历史最大逾期天数:biz_maxDpd
        'biz_maxDpd': await biz_maxDpd,
        # 最近一笔结清交易逾期天数:biz_lastOrderDpd
        'biz_lastOrderDpd': await biz_lastOrderDpd,
        # 已结清订单逾期天数最大值:max_overdue_days
        'max_overdue_days': await max_overdue_days,
        # 白名单首次日期距离申请时间天数校验:whitelist_first_time_diff_days
        'whitelist_first_time_diff_days': await whitelist_first_time_diff_days,
        # 本平台近一周该客户放款笔数:week_number_of_loans
        'week_number_of_loans': await week_number_of_loans,
        # 本平台当前逾期情况:now_overdue_num
        'now_overdue_num': await now_overdue_num,
        # 当前产品的在贷未还笔数:on_loan_not_yet_cnt
        'on_loan_not_yet_cnt': await on_loan_not_yet_cnt,
        # 本平台近一周内申请订单数:week_apply_num
        'week_apply_num': await week_apply_num,
        # 全平台当前未还交易逾期天数（基于注册号码）:biz_max_expire_day_all_phone_cur
        'biz_max_expire_day_all_phone_cur': await biz_max_expire_day_all_phone_cur,
        # 全平台最大逾期天数（基于注册号码）:biz_max_expire_day_all_phone_history
        'biz_max_expire_day_all_phone_history': await biz_max_expire_day_all_phone_history,
        # 全平台最大逾期天数（基于最终身份证号）:biz_max_expire_day_all_real_idcard_history
        'biz_max_expire_day_all_real_idcard_history': await biz_max_expire_day_all_real_idcard_history,
        # 手机号命中黑名单数:inner_black_phone
        'inner_black_phone': await inner_black_phone,
        # 身份证命中黑名单数:inner_black_idCard
        'inner_black_idCard': await inner_black_idCard,
    })
    # print("update 程序运行时长：", time.time() - start_time, "秒")
    return data


if __name__ == '__main__':
    # 记录程序开始时间
    start_time = time.time()

    apply_no = '171099052027659554'
    # 订单数据
    order_dict = utils_sql.get_mysql_order(apply_no)
    print(order_dict)

    # 根据订单获取json文件路径
    json_file_url = utils_sql.get_mysql_json_url(apply_no)
    # 远程获取JSON数据
    json_string = utils.load_remote_json(json_file_url)
    # 将字符串转成json对象
    json_dict = utils.parse_json(json_string)
    print("json_dict 程序运行时长：", time.time() - start_time, "秒")

    # 设备信息 相关参数（业务参数）计算
    device_string = json_dict.get('deviceInfo')
    device_dict = utils.parse_json(device_string) if utils.is_json_string(device_string) else None
    biz_features = asyncio.run(cal_biz_features(
        apply_no=order_dict.get('apply_no'),
        phone=order_dict.get('phone'),
        apply_date=order_dict.get('apply_date'),
        app_id=order_dict.get('app_id'),
        user_id=order_dict.get('user_id'),
        project_id=order_dict.get('project_id'),
        device_dict=device_dict,
        enable=True
    ))
    print("biz_features 程序运行时长：", time.time() - start_time, "秒")
    print(biz_features)
